package com.cloudinnov.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author chengning
 * @date 2017年3月2日下午2:03:50
 * @email ningcheng@cloudinnov.com
 * @remark
 * @version Copyright: Copyright (c) 2011 Company:中科云创科技有限公司
 */
public class PostgreSqlDbUtil {
	private final static Logger LOG = LoggerFactory.getLogger(PostgreSqlDbUtil.class);
	// 数据库用户名
	public static String USERNAME;
	// 数据库密码
	public static String PASSWORD;
	// 驱动信息
	public static String DRIVER;
	// 数据库地址
	public static String URL;
	private Connection connection;
	private PreparedStatement pstmt;
	private ResultSet resultSet;

	public PostgreSqlDbUtil() {
		// TODO Auto-generated constructor stub
		try {
			USERNAME = PropertiesUtils.findPropertiesKey("postgreSql.username");
			PASSWORD = PropertiesUtils.findPropertiesKey("postgreSql.password");
			DRIVER = PropertiesUtils.findPropertiesKey("postgreSql.driver");
			URL = PropertiesUtils.findPropertiesKey("postgreSql.url");
			Class.forName(DRIVER);
			LOG.debug("{} 数据库连接成功！", URL);
		} catch (Exception e) {
		}
	}
	/**
	 * 获得数据库的连接
	 * @return
	 */
	public Connection getConnection() {
		try {
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			LOG.debug("获取数据库连接成功, {}, {}", URL, USERNAME);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	/**
	 * 增加、删除、改
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
		boolean flag = false;
		int result = -1;
		pstmt = connection.prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}
	/**
	 * 查询单条记录
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}
		return map;
	}
	/**
	 * 查询多条记录
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		return list;
	}
	/**
	 * 通过反射机制查询单条记录
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> T findSimpleRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {
		T resultObject = null;
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
		}
		return resultObject;
	}
	/**
	 * 通过反射机制查询多条记录
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> findMoreRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			T resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				String[] args = cols_name.split("_");
				if (args.length > 1) {
					cols_name = "";
				}
				for (int k = 0; k < args.length; k++) {
					if (k > 0) {
						cols_name += args[k - 1] + args[k].substring(0, 1).toUpperCase()
								+ args[k].substring(1, args[k].length());
					}
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
			list.add(resultObject);
		}
		return list;
	}
	/**
	 * 释放数据库连接
	 */
	public void releaseConn() {
		if (resultSet != null) {
			try {
				resultSet.close();
				if (pstmt != null) {
					pstmt.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void main(String[] args)
			throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		String sql = "SELECT camera.camera_id, camera.index_code, camera.name, camera.matrix_code, "
				+ "  device.device_id, device.network_addr, device.network_port, control.name AS controlname "
				+ "  FROM camera_info camera JOIN device_info device ON device.device_id = camera.device_id "
				+ "  JOIN control_unit control ON control.control_unit_id = camera.control_unit_id "
				+ "  WHERE device.status = 0";
		PostgreSqlDbUtil jdbcUtils = new PostgreSqlDbUtil();
		jdbcUtils.getConnection();
		List<Map<String, Object>> list;
		try {
			list = jdbcUtils.findModeResult(sql, null);
			System.out.println(list);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		jdbcUtils.releaseConn();
		/*
		 * // 连接字符串，格式： "jdbc:数据库驱动名称://数据库服务器ip/数据库名称" String url =
		 * "jdbc:postgresql://10.152.212.2/port"; String username = "gv"; String password =
		 * "hik12345+"; Class.forName("org.postgresql.Driver").newInstance(); Connection conn =
		 * DriverManager.getConnection(url, username, password); Statement stmt =
		 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		 * ResultSet rs = stmt.executeQuery(sql); while (rs.next()) {
		 * System.out.println(rs.getString(1)); } rs.close(); stmt.close(); conn.close();
		 */
	}
}
